"""数据库连接池管理模块
提供高效的数据库连接池管理，优化数据库连接资源利用
"""
import os
import logging
import traceback
import json
import time
from sqlalchemy import create_engine, event
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.pool import QueuePool
from sqlalchemy.exc import SQLAlchemyError
from config.config import get_config
# 延迟导入避免循环依赖
# from services.shared.log_utils import log_error, log_info, log_system_event

# 获取配置
config = get_config()

# 获取日志记录器
logger = logging.getLogger(__name__)

# 连接池统计
pool_stats = {
    'created_connections': 0,
    'closed_connections': 0,
    'active_sessions': 0,
    'last_reset': time.time()
}

class DatabasePool:
    """数据库连接池管理类"""
    
    _instance = None
    
    def __new__(cls):
        if cls._instance is None:
            cls._instance = super(DatabasePool, cls).__new__(cls)
            cls._instance._initialized = False
            cls._instance.engine = None
            cls._instance.session_factory = None
            cls._instance.scoped_session = None
        return cls._instance
    
    def _initialize(self):
        """初始化数据库连接池"""
        try:
            # 获取数据库URL
            database_url = os.getenv('DATABASE_URL', config.DATABASE_URL)
            
            # 创建引擎，配置连接池
            # 考虑到多个服务实例，大幅减少每个服务的连接池大小以避免超过MySQL最大连接数
            # 7个服务 × 每服务最多10个连接 = 70个连接，远低于300的限制
            self.engine = create_engine(
                database_url,
                poolclass=QueuePool,
                pool_size=3,  # 连接池大小（从8减少到3）
                max_overflow=7,  # 最大溢出连接数（从32减少到7）
                pool_timeout=30,  # 连接超时时间
                pool_recycle=900,  # 连接回收时间（15分钟，从30分钟减少）
                pool_pre_ping=True,  # 连接前检查连接有效性
                echo=False,  # 是否打印SQL语句
                connect_args={
                    'charset': 'utf8mb4',
                    'use_unicode': True
                }
            )
            
            # 添加连接池事件监听
            @event.listens_for(self.engine, "connect")
            def receive_connect(dbapi_connection, connection_record):
                """连接创建时的回调"""
                pool_stats['created_connections'] += 1
                logger.debug(f"新建数据库连接，总创建数: {pool_stats['created_connections']}")

            @event.listens_for(self.engine, "close")
            def receive_close(dbapi_connection, connection_record):
                """连接关闭时的回调"""
                pool_stats['closed_connections'] += 1
                logger.debug(f"关闭数据库连接，总关闭数: {pool_stats['closed_connections']}")
            
            # 创建会话工厂
            self.session_factory = sessionmaker(bind=self.engine)
            
            # 创建线程安全的会话
            self.scoped_session = scoped_session(self.session_factory)
            
            # 标记初始化完成
            self._initialized = True
        except Exception as e:
            logger.error(f"数据库连接池初始化失败: {str(e)}")
            
            # 记录详细的系统错误日志
            try:
                from services.shared.log_utils import log_error
                log_error(
                    "database",
                    "数据库连接池初始化失败",
                    {
                        "database_url": database_url[:50] + "..." if len(database_url) > 50 else database_url,
                        "error_type": type(e).__name__,
                        "error_message": str(e),
                        "stack_trace": traceback.format_exc(),
                        "execution_context": {
                            "function": "_initialize",
                            "module": "shared.db_pool",
                            "operation": "database_pool_initialization"
                        }
                    }
                )
            except ImportError:
                pass
            raise
    
    def get_session(self):
        """获取数据库会话"""
        # 延迟初始化：只有在真正需要时才建立数据库连接
        if not self._initialized:
            self._initialize()
        
        try:
            session = self.scoped_session()
            pool_stats['active_sessions'] += 1
            logger.debug("成功获取数据库会话")
            return session
        except SQLAlchemyError as e:
            logger.error(f"获取数据库会话失败: {str(e)}")
            
            # 记录详细的系统错误日志
            try:
                from services.shared.log_utils import log_error
                log_error(
                    "database",
                    "获取数据库会话失败",
                    {
                        "error_type": type(e).__name__,
                        "error_message": str(e),
                        "stack_trace": traceback.format_exc(),
                        "execution_context": {
                            "function": "get_session",
                            "module": "shared.db_pool",
                            "operation": "database_session_creation"
                        }
                    }
                )
            except ImportError:
                pass
            raise
    
    def close_session(self):
        """关闭当前线程的数据库会话"""
        # 如果还未初始化，则无需关闭
        if not self._initialized or not self.scoped_session:
            return
            
        try:
            self.scoped_session.remove()
            pool_stats['active_sessions'] = max(0, pool_stats['active_sessions'] - 1)
            logger.debug("成功关闭数据库会话")
        except Exception as e:
            logger.error(f"关闭数据库会话失败: {str(e)}")
            
            # 记录详细的系统错误日志
            try:
                from services.shared.log_utils import log_error
                log_error(
                    "database",
                    "关闭数据库会话失败",
                    {
                        "error_type": type(e).__name__,
                        "error_message": str(e),
                        "stack_trace": traceback.format_exc(),
                        "execution_context": {
                            "function": "close_session",
                            "module": "shared.db_pool",
                            "operation": "database_session_closure"
                        }
                    }
                )
            except ImportError:
                pass
            # 不重新抛出异常，因为关闭会话失败不应该影响主要业务逻辑

def get_pool_stats():
    """获取连接池统计信息的便捷函数"""
    return db_pool.get_pool_stats()

def reset_pool_stats():
    """重置连接池统计的便捷函数"""
    db_pool.reset_pool_stats()
    
    def get_engine(self):
        """获取数据库引擎"""
        return self.engine
    
    def get_pool_stats(self):
        """获取连接池统计信息"""
        current_time = time.time()
        uptime = current_time - pool_stats['last_reset']
        
        # 基础统计信息
        stats = {
            'created_connections': pool_stats['created_connections'],
            'closed_connections': pool_stats['closed_connections'],
            'active_sessions': pool_stats['active_sessions'],
            'uptime_seconds': int(uptime)
        }
        
        # 如果连接池已初始化，添加详细的连接池状态
        if self._initialized and self.engine:
            try:
                pool = self.engine.pool
                stats.update({
                    'pool_size': pool.size(),
                    'checked_in': pool.checkedin(),
                    'checked_out': pool.checkedout(),
                    'overflow': pool.overflow()
                })
            except Exception as e:
                logger.warning(f"获取连接池详细状态失败: {str(e)}")
                stats.update({
                    'pool_size': 0,
                    'checked_in': 0,
                    'checked_out': 0,
                    'overflow': 0
                })
        else:
            stats.update({
                'pool_size': 0,
                'checked_in': 0,
                'checked_out': 0,
                'overflow': 0
            })
        
        return stats
    
    def reset_pool_stats(self):
        """重置连接池统计"""
        pool_stats.update({
            'created_connections': 0,
            'closed_connections': 0,
            'active_sessions': 0,
            'last_reset': time.time()
        })
        logger.info("连接池统计已重置")

# 创建全局数据库连接池实例
db_pool = DatabasePool()

def get_db_session():
    """获取数据库会话的便捷函数"""
    try:
        session = db_pool.get_session()
        return session
    except Exception as e:
        logger.error(f"全局获取数据库会话失败: {str(e)}")
        
        # 记录详细的系统错误日志
        try:
            from services.shared.log_utils import log_error
            log_error(
                "database",
                "全局获取数据库会话失败",
                {
                    "error_type": type(e).__name__,
                    "error_message": str(e),
                    "stack_trace": traceback.format_exc(),
                    "execution_context": {
                        "function": "get_db_session",
                        "module": "shared.db_pool",
                        "operation": "global_database_session_creation"
                    }
                }
            )
        except ImportError:
            pass
        raise

def close_db_session():
    """关闭数据库会话的便捷函数"""
    try:
        db_pool.close_session()
    except Exception as e:
        logger.error(f"全局关闭数据库会话失败: {str(e)}")
        
        # 记录详细的系统错误日志
        try:
            from services.shared.log_utils import log_error
            log_error(
                "database",
                "全局关闭数据库会话失败",
                {
                    "error_type": type(e).__name__,
                    "error_message": str(e),
                    "stack_trace": traceback.format_exc(),
                    "execution_context": {
                        "function": "close_db_session",
                        "module": "shared.db_pool",
                        "operation": "global_database_session_closure"
                    }
                }
            )
        except ImportError:
            pass
        # 不重新抛出异常，因为关闭会话失败不应该影响主要业务逻辑